In our daily work, we always encounter this problem that it is really a tough work to view or print a worksheet which contains hundreds of rows and columns. Numerous data in one worksheet makes us dazzled and annoyed. In such a case, break one excel worksheet to multiple pages is the best choice to make things easy.
Image that we split the worksheet to pages of ten or twenty columns, we have no difficulties whatever read or print this worksheet. No matter who meet this problem, as long as you come here, it will be solved in minutes.
At the very beginning, I use a professional Excel component Spire.XLS as my application. It supports C#, VB.NET, ASP.NET, ASP.NET MVC and Silverlight. So if necessary, please freely install the Newest Version Spire. XLS 6.5.9.4 on system.
Image that we split the worksheet to pages of ten or twenty columns, we have no difficulties whatever read or print this worksheet. No matter who meet this problem, as long as you come here, it will be solved in minutes.
At the very beginning, I use a professional Excel component Spire.XLS as my application. It supports C#, VB.NET, ASP.NET, ASP.NET MVC and Silverlight. So if necessary, please freely install the Newest Version Spire. XLS 6.5.9.4 on system.
How to break one Excel worksheet to Multiple Pages with C#, VB.NET
Using Spire.XLS, the whole task will be realized in Three simple steps. Please look at the below procedure.
Step1. Create a new project.
1. Create a new project in Visual Studio.
2. Choose .NET Framework 2 or above to be the Target framework.
3. Add Spire.XLS DLL as reference in Project.
Step2. Break one Excel worksheet to multiple pages with C#, VB.NET
1. Create a new workbook and load an Excel file from system.
C# Code:
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"D:\michelle\DatatableSample.xlsx");
Worksheet sheet = workbook.Worksheets[0];
VB.NET Code:
Dim workbook As New Workbook()
workbook.LoadFromFile("D:\michelle\DatatableSample.xlsx")
Dim sheet As Worksheet = workbook.Worksheets(0)
2. Break the Excel worksheet to multiple pages and set view mode as page break preview.
C# Code:
workbook.Worksheets[0].HPageBreaks.Add(sheet.Range["A7"]);
workbook.Worksheets[0].HPageBreaks.Add(sheet.Range["A13"]);
workbook.Worksheets[0].HPageBreaks.Add(sheet.Range["A19"]);
workbook.Worksheets[0].VPageBreaks.Add(sheet.Range["I1"]);
workbook.Worksheets[0].ViewMode = ViewMode.Preview;
VB.NET Code:
workbook.Worksheets(0).HPageBreaks.Add(sheet.Range("A7"))
workbook.Worksheets(0).HPageBreaks.Add(sheet.Range("A13"))
workbook.Worksheets(0).HPageBreaks.Add(sheet.Range("A19"))
workbook.Worksheets(0).VPageBreaks.Add(sheet.Range("I1"))
workbook.Worksheets(0).ViewMode = ViewMode.Preview
C# Code:
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"D:\michelle\DatatableSample.xlsx");
Worksheet sheet = workbook.Worksheets[0];
VB.NET Code:
Dim workbook As New Workbook()
workbook.LoadFromFile("D:\michelle\DatatableSample.xlsx")
Dim sheet As Worksheet = workbook.Worksheets(0)
2. Break the Excel worksheet to multiple pages and set view mode as page break preview.
C# Code:
workbook.Worksheets[0].HPageBreaks.Add(sheet.Range["A7"]);
workbook.Worksheets[0].HPageBreaks.Add(sheet.Range["A13"]);
workbook.Worksheets[0].HPageBreaks.Add(sheet.Range["A19"]);
workbook.Worksheets[0].VPageBreaks.Add(sheet.Range["I1"]);
workbook.Worksheets[0].ViewMode = ViewMode.Preview;
VB.NET Code:
workbook.Worksheets(0).HPageBreaks.Add(sheet.Range("A7"))
workbook.Worksheets(0).HPageBreaks.Add(sheet.Range("A13"))
workbook.Worksheets(0).HPageBreaks.Add(sheet.Range("A19"))
workbook.Worksheets(0).VPageBreaks.Add(sheet.Range("I1"))
workbook.Worksheets(0).ViewMode = ViewMode.Preview
Step3. Save and launch the Excel file.C# Code:
workbook.SaveToFile("ExcelPageBreak.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("ExcelPageBreak.xlsx");
VB.NET Code:
workbook.SaveToFile("ExcelPageBreak.xlsx", ExcelVersion.Version2010)
System.Diagnostics.Process.Start("ExcelPageBreak.xlsx")
Preview
Now you can never be bothered by rows of data in one worksheet. You can use the above method to deal with the problem in a few minutes by using Spire.XLS. If you also have other tough problems related to Excel, please see More Excel Functions.